## 
# Load city referendums
city <- read.csv("Data/Raw data/City_referendums.csv")

# Remove those referendums without match
city <- city[!is.na(city$City_geoid),]
city$City_geoid <- sprintf("%07d",city$City_geoid)

city$date <- as.Date(city$date)
city$year <- as.numeric(substr(city$date,1,4))

city$chg_prop.itm <- city$itm_prop/city$nb - city$itm_prop18/city$nb18
city$percent_yes <- city$vote_yes/city$vote_total*100
city$post <- ifelse(city$year>=2019,1,0)
city$Pass <- ifelse(city$percent_yes>=city$threshold*100,1,0)


city$election <- substr(city$date,1,7)
city$presidential_el <- ifelse(city$election %in% c("2008-11","2012-11","2016-11","2020-11"),1,0)
city$midterm_el <- ifelse(city$election %in% c("2010-11","2014-11","2018-11","2022-11"),1,0)
city$odd_year <- ifelse(city$year %% 2 ==1,1,0)

city$election_FE <- as.factor(ifelse(city$odd_year == 1, city$year,ifelse(substr(city$date,6,7) == "11",as.character(city$election),city$year)))

# Clean types of referendums
city$type <- ifelse(city$type == "Mello/Roos Bond","GO Bond",city$type)
city$type <- ifelse(city$type %in% c("Revenue Bond"),"Miscellaneous Tax",city$type)
city$type <- ifelse(city$type %in% c("Gasoline Tax"),"Miscellaneous Tax",city$type)
city$type <- ifelse(city$type %in% c("Development Tax"),"Miscellaneous Tax",city$type)


## Merging voting population
acs <- read.csv("Data/raw data/ACS_City_2010-2021.csv")
acs <- acs[acs$population_votingage>0,]
acs <- acs[,c("population_votingage","GEOID","year")]

acs$City_geoid <- sprintf("%07d",acs$GEOID)
acs$GEOID <- NULL

city <- merge(city,acs,by = c("year","City_geoid"),all.x = TRUE)
rm(acs)

###########
# Create failed recently variable

city$election <- as.Date(paste(substr(city$date,1,7),"01",sep = "-"),format = "%Y-%m-%d")


city$last_el <- as.Date("2007-01-01")
city$last_win <- as.Date("2007-01-01")

for(i in 1:nrow(city)){
  
  el <- city[i,"election"]
  city_id <- city[i,"City_geoid"]
  
  all <- city[city$election < el & city$City_geoid == city_id,]
  
  if(nrow(all) !=0 ){
    city[i,"last_el"] <- max(all$election) # take the most recent
    
    allwin <- all[all$Pass == 1,]
    
    if(nrow(allwin) != 0){
      city[i,"last_win"] <- max(allwin$election)
    }
  }
  
  
}
rm(i,allwin,all,el,city_id)

city$tsince_last_el <- city$year - as.numeric(substr(city$last_el,1,4))
city$failed_recently <- ifelse(city$last_el != city$last_win & city$tsince_last_el < 4,1,0)
city$tsince_last_el <- NULL
############

city$turnout <- Winsorize(city$vote_total/city$population_votingage,minval = 0,maxval = 1)
city$SalesTax <- city$SalesTax*100
city$property_tax <- ifelse(city$type %in% c("Property Tax"),1,0)
city <- city[city$property_tax ==0,]

city <- city[,c("year","City_geoid","date","type","percent_yes","SalesTax","chg_prop.itm","post","Pass","presidential_el","midterm_el","odd_year","election_FE","population_votingage","failed_recently","turnout")]
